package org.hcf.utils.excel;

import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


import java.io.*;
import java.util.*;

public class JsonListToExcel {
    /** 模板文件的绝对路径 */
    private String tplFilePath;
    private File tplFile;

    /** 模板key所在的行*/
    private int jsonKeyRowNum = 1;
    /** 模板key开始的列 （从0开始）,默认是0 */
    private int jsonKeyRowColStart = 0;
    /** json每一列对应哪个key ：key=json的key，val=jsonkey所在的列*/
    private Map jsonKeys = new HashMap();

    /** josnkey对应的key的注释 */
    private Map jsonKeysDesc = new HashMap();
    /** jsonKey对应的注释所在的行（从0开始）,默认是0  */
    private int jsonKeysDescRowNum = 0;

    /**  长度不大于16位（不以数字开头的数字，字母下划线组成的key）*/
    private String jsonKeyReg = "^\\$\\$[a-z|A-Z|_][a-z|A-Z|_|0-9]{0,15}\\$\\$$";


    /** 输出文件的绝对路径 */
    private String outPutFilePath;
    private File outPutFile;
    /** 数据集合:jsonObject的集合 */
    private List jsons;
    /** 数据开始的行（从0开始）,默认是1 */
    private int dataRowNumStart = 1;
    /** 数据开始的列（从0开始）,需要keyColNumStart一致 */
    private int dataColNumStart ;
    /** 数据最多多少行 */
    private int maxDataRowNums = Integer.MAX_VALUE;

    /**  */
    private String sheetName;

    public boolean executeJson2Excel(){
        boolean retFlag= false;
        this.initJsonKeys();

        //读取模板文件
        Workbook wb = null;
        WritableWorkbook workbook = null;
        try {
            this.createOutPutFileByTplFile();
            //获得Excel文件
            wb = Workbook.getWorkbook(this.getOutPutFile());
            workbook = Workbook.createWorkbook(this.getOutPutFile(), wb);
            WritableSheet sheet = workbook.getSheet(0);//获取第一个工作簿
            Iterator it = this.getJsons().iterator();
            int rowNum = dataRowNumStart ;//行号
            /*遍历jsonList*/
            while ( it.hasNext() ){
                JsonObject jsonObject = (JsonObject)it.next();
                /*遍历列，一行一行插入数据*/
                Set<Map.Entry<String,JsonElement>> sets = jsonObject.entrySet();//
                for(Map.Entry<String,JsonElement> ent : sets){
                    String key = ent.getKey();
                    String value = ent.getValue()!=null?ent.getValue().toString():"";
                    String colNumStr=(String) jsonKeys.get(key);
                    if( colNumStr!=null && !"".equals(colNumStr.trim()) ){
                        int colNum =Integer.valueOf(colNumStr).intValue();
                        Label label=new Label(colNum,rowNum,value);
                        sheet.addCell(label);
                    }
                }
//                org.json.json 的解析方式
//                Iterator keys = jsonObject.keys();
//                while(keys.hasNext()){
//                    String key = (String) keys.next();
//                    String value = String.valueOf(jsonObject.get(key));
//                    String colNumStr=(String) jsonKeys.get(key);
//                    if( colNumStr!=null && !"".equals(colNumStr.trim()) ){
//                        int colNum =Integer.valueOf(colNumStr).intValue();
//                        Label label=new Label(colNum,rowNum,value);
//                        sheet.addCell(label);
//                    }
//                }
                rowNum++;
            }
            workbook.write();
            retFlag = true;
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } finally {
            try {
                if( workbook!=null ) { workbook.close(); }
                if( wb!=null ){ wb.close();  }
            } catch (IOException e) {
                e.printStackTrace();
            } catch (WriteException e) {
                e.printStackTrace();
            }
        }
        return retFlag;
    }

    /** 初始化json对象的key */
    public boolean initJsonKeys(){
        boolean retFlag = false;
//        读取模板文件
        Workbook workbook= null;
        try {
            workbook=Workbook.getWorkbook(this.getTplFile());
            Sheet sheet=workbook.getSheet(0);//获取第一个工作簿
            int rows = sheet.getRows();
            int cols = sheet.getColumns();
//            $$jsonKey$$
            String keyReg = this.jsonKeyReg;//长度不大于16位（不以数字开头的数字，字母下划线组成的key）
            if(rows<this.getJsonKeyRowNum()){
                new RuntimeException("给定或默认的jsonKeys所在行号与模板文件不符合");
            }
//           行是固定的行，列
            for(int col=jsonKeyRowColStart;col<cols;col++){
                // 遍历列
                Cell cell=sheet.getCell(col,this.getJsonKeyRowNum());
                String cellContent = cell.getContents();
                if(cellContent.matches(keyReg)){//符合key的正则表达式
//                    key=列号 ，val =去掉$符号的值
                    this.jsonKeys.put( String.valueOf(cellContent.replaceAll("\\$","")),String.valueOf(col) );
                    Cell descCell = sheet.getCell(col,this.getJsonKeysDescRowNum());
                    this.getJsonKeysDesc().put( String.valueOf(cellContent.replaceAll("\\$","")),descCell.getContents() );
                    retFlag = true;
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }finally{
            if(workbook != null){workbook.close();}
        }
        return retFlag;
    }
    /** 将模板文件复制到目标文件中去 */
    public boolean createOutPutFileByTplFile() throws IOException {
        boolean retFlag = false;
        File originFile = this.getTplFile();//源文件
        File goalFile = this.getOutPutFile();//目标文件
        if(goalFile.exists()){
            goalFile.delete();
        }
        goalFile.createNewFile();
        /* 复制文件 */
        InputStream inStream = new FileInputStream(originFile); //读入原文件
        FileOutputStream fs = new FileOutputStream(goalFile);
        int byteread = 0;//真正读到的字节大小
        byte[] buffer = new byte[1024];//每次读的字节存放到该文件中
        int length;
        while ( (byteread = inStream.read(buffer)) != -1) {
            fs.write(buffer, 0, byteread);
        }
        inStream.close();
        fs.close();
        retFlag = true;
        return retFlag;
    }

    public JsonListToExcel(){
        super();
    }

    public JsonListToExcel(String tplFilePath, String outPutFilePath){
        super();
        init(tplFilePath,outPutFilePath);
    }

    public void init(String tplFilePath, String outPutFilePath){
        this.setTplFilePath(tplFilePath);
        this.setTplFile( new File(this.getTplFilePath()) );
        this.setOutPutFilePath(outPutFilePath);
        this.setOutPutFile( new File(this.getOutPutFilePath()) );
        if(!this.getTplFile().exists()){
            throw new RuntimeException("模板文件不存在");
        }
    }

    public String getTplFilePath() {
        return tplFilePath;
    }

    public void setTplFilePath(String tplFilePath) {
        this.tplFilePath = tplFilePath;
    }

    public File getTplFile() {
        return tplFile;
    }

    public void setTplFile(File tplFile) {
        this.tplFile = tplFile;
    }

    public int getJsonKeyRowNum() {
        return jsonKeyRowNum;
    }

    public void setJsonKeyRowNum(int jsonKeyRowNum) {
        this.jsonKeyRowNum = jsonKeyRowNum;
    }

    public int getJsonKeyRowColStart() {
        return jsonKeyRowColStart;
    }

    public void setJsonKeyRowColStart(int jsonKeyRowColStart) {
        this.jsonKeyRowColStart = jsonKeyRowColStart;
    }

    public Map getJsonKeys() {
        return jsonKeys;
    }

    public void setJsonKeys(Map jsonKeys) {
        this.jsonKeys = jsonKeys;
    }

    public Map getJsonKeysDesc() {
        return jsonKeysDesc;
    }

    public void setJsonKeysDesc(Map jsonKeysDesc) {
        this.jsonKeysDesc = jsonKeysDesc;
    }

    public int getJsonKeysDescRowNum() {
        return jsonKeysDescRowNum;
    }

    public void setJsonKeysDescRowNum(int jsonKeysDescRowNum) {
        this.jsonKeysDescRowNum = jsonKeysDescRowNum;
    }

    public String getOutPutFilePath() {
        return outPutFilePath;
    }

    public void setOutPutFilePath(String outPutFilePath) {
        this.outPutFilePath = outPutFilePath;
    }

    public File getOutPutFile() {
        return outPutFile;
    }

    public void setOutPutFile(File outPutFile) {
        this.outPutFile = outPutFile;
    }

    public List getJsons() {
        return jsons;
    }

    public void setJsons(List jsons) {
        this.jsons = jsons;
    }

    public int getDataRowNumStart() {
        return dataRowNumStart;
    }

    public void setDataRowNumStart(int dataRowNumStart) {
        this.dataRowNumStart = dataRowNumStart;
    }

    public int getDataColNumStart() {
        return dataColNumStart;
    }

    public void setDataColNumStart(int dataColNumStart) {
        this.dataColNumStart = dataColNumStart;
    }

    public int getMaxDataRowNums() {
        return maxDataRowNums;
    }

    public void setMaxDataRowNums(int maxDataRowNums) {
        this.maxDataRowNums = maxDataRowNums;
    }

    public String getJsonKeyReg() {
        return jsonKeyReg;
    }

    public void setJsonKeyReg(String jsonKeyReg) {
        this.jsonKeyReg = jsonKeyReg;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }
}
